/**
 * 2018年12月4日上午10:25:15
 * @author 谢进财
 */
package com.baihang.project.ds.service;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import com.baihang.framework.dao.Record;
import com.baihang.framework.kit.DownLoadKit;
import com.baihang.framework.kit.JsonKit;
import com.baihang.framework.kit.PathKit;
import com.baihang.framework.service.BaseService;
import com.baihang.project.ds.dao.FangYuanDao;
import com.baihang.project.ds.dao.ReportDao;
import com.baihang.project.ds.model.FangYuanModel;

@Service
public class ReportService extends BaseService {
	@Autowired
	private ReportDao dao;
	@Autowired
	private FangYuanDao fydao;

	public String getGrid() {
		// List<FangYuanModel> listModel = fydao.queryForFangYuanList("select
		// r.*,b.D_D_BanName as D_BanName from room as r left JOIN Bans b on r.BanID
		// =b.D_ID");
		List<FangYuanModel> listModel = fydao.queryForFangYuanList("select * from room ");
		int count = listModel.size();
		// List<Record> list = dao.queryForRecordList("select r.*,b.D_D_BanName as
		// D_BanName from room as r left JOIN Bans b on r.BanID =b.D_ID");
		List<Record> list = dao.queryForRecordList("select * from room ");

		// 查询room表列名
		List<Record> listColumnName = dao.queryForTableColumnName("ds", "room");
		// 构建空行
		Record nullRecord = new Record();
		for (Record rec : listColumnName) {
			nullRecord.getColumns().put(rec.getStr("column_name"), "");
		}
		// list.add(nullRecord); //移到后面进行Add 避免影响行数
		// 构建标头
		Record titeRecord = new Record();
		for (Record rec : listColumnName) {
			if ("D_BanName".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_BanName", "楼号");
			} else if ("D_Unit".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Unit", "总套数");
			} else if ("D_position".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_position", "内控");
			} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_RoomNo", "已售");
			} else if ("D_Price".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Price", "待售");
			} else if ("D_Total".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Total", "总价统计");
			} else {
				titeRecord.getColumns().put(rec.getStr("column_name"), "");
			}
		}
		// list.add(titeRecord);
		// 进行分组
		Map<String, List<FangYuanModel>> groupMap = listModel.stream()
				.collect(Collectors.groupingBy(FangYuanModel::getD_BanName));

		List<Record> groupRecords = new ArrayList<Record>();// 存放分组查询的数据

		for (Entry<String, List<FangYuanModel>> entry : groupMap.entrySet()) {// 遍历分组情况
			String key = entry.getKey();
			List<FangYuanModel> listRecord = entry.getValue();
			Record groupRecord = new Record();
			for (Record rec : listColumnName) {
				if ("D_BanName".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_BanName", key);// 楼号
				} else if ("D_Unit".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_Unit", listRecord.size());// 总套数
				} else if ("D_position".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_position",
							listRecord.stream().filter(FangYuanModel -> "1".equals(FangYuanModel.getD_Monitor())).count());// 内控
				} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_RoomNo",
							listRecord.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2").count());// 已销售
				} else if ("D_Price".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_Price",
							listRecord.stream().filter(FangYuanModel -> FangYuanModel.getD_state() != "2").count());// 待售
				} else if ("D_Total".equals(rec.getStr("column_name"))) {
					groupRecord.getColumns().put("D_Total",
							listRecord.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2")
									.mapToDouble(FangYuanModel::getD_Total).sum());// 总额
				} else {
					groupRecord.getColumns().put(rec.getStr("column_name"), "");
				}
			}
			groupRecords.add(groupRecord);
		}

		// int sum = (int) list.stream().mapToInt(Record::getIntColumn1).count();
		// 查询总合
		Record totalRecord = new Record();
		for (Record rec : listColumnName) {
			if ("D_BanName".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_BanName", "合计");
			} else if ("D_Unit".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Unit", listModel.size());// 总套数
			} else if ("D_position".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_position",
						listModel.stream().filter(FangYuanModel ->!"0".equals(FangYuanModel.getD_Monitor())).count());
			} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_RoomNo",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2").count());
			} else if ("D_Price".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Price",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() != "2").count());
			} else if ("D_Total".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Total",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2")
								.mapToDouble(FangYuanModel::getD_Total).sum());// 总额
			} else if ("D_state".equals(rec.getStr("D_state"))) {
				totalRecord.getColumns().put("D_state", -1);
			} else {
				totalRecord.getColumns().put(rec.getStr("column_name"), "");
			}
		}

		// 整合进入list集合
		list.add(nullRecord); // 空白
		list.add(titeRecord); // 标头
		list.addAll(groupRecords);
		list.add(totalRecord);

		String jsonRecord = JsonKit.listToJson(list, 1);
		StringBuilder sb = new StringBuilder("{");
		sb.append("\"Rows\":" + jsonRecord + ",");
		sb.append("\"Total\":" + count + 2);
		sb.append("}");

		return sb.toString();
	}

	public String getGridByLd(int id) {
		// List<FangYuanModel> listModel =
		// fydao.queryForFangYuanList(String.format("select r.*,b.D_D_BanName as
		// D_BanName from room as r left JOIN Bans b on r.BanID =b.D_ID where
		// r.BanID=%d", id));
		List<FangYuanModel> listModel = fydao
				.queryForFangYuanList(String.format("select * from room  where BanID=%d", id));
		int count = listModel.size();
		// List<Record> list = dao.queryForRecordList(String.format("select
		// r.*,b.D_D_BanName as D_BanName from room as r left JOIN Bans b on r.BanID
		// =b.D_ID where r.BanID=%d", id));
		List<Record> list = dao.queryForRecordList(String.format("select * from room  where BanID=%d", id));

		// 查询room表列名
		List<Record> listColumnName = dao.queryForTableColumnName("ds", "room");
		// 构建空行
		Record nullRecord = new Record();
		for (Record rec : listColumnName) {
			nullRecord.getColumns().put(rec.getStr("column_name"), "");
		}
		// list.add(nullRecord); //移到后面进行Add 避免影响行数
		// 构建标头
		Record titeRecord = new Record();
		for (Record rec : listColumnName) {
			if ("D_BanName".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_BanName", "楼号");
			} else if ("D_Unit".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Unit", "总套数");
			} else if ("D_position".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_position", "内控");
			} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_RoomNo", "已售");
			} else if ("D_Price".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Price", "待售");
			} else if ("D_Total".equals(rec.getStr("column_name"))) {
				titeRecord.getColumns().put("D_Total", "总价统计");
			} else {
				titeRecord.getColumns().put(rec.getStr("column_name"), "");
			}
		}
		// list.add(titeRecord);

		Record groupRecord = new Record();
		for (Record rec : listColumnName) {
			if ("D_BanName".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_BanName", listModel.get(0).getD_BanName());// 楼号
			} else if ("D_Unit".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_Unit", listModel.size());// 总套数
			} else if ("D_position".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_position",
						listModel.stream().filter(FangYuanModel -> "1".equals(FangYuanModel.getD_Monitor())).count());// 内控
			} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_RoomNo",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2").count());// 已销售
			} else if ("D_Price".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_Price",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() != "2").count());// 待售
			} else if ("D_Total".equals(rec.getStr("column_name"))) {
				groupRecord.getColumns().put("D_Total",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2")
								.mapToDouble(FangYuanModel::getD_Total).sum());// 总额
			} else {
				groupRecord.getColumns().put(rec.getStr("column_name"), "");
			}
		}

		// int sum = (int) list.stream().mapToInt(Record::getIntColumn1).count();
		// 查询总合
		Record totalRecord = new Record();
		for (Record rec : listColumnName) {
			if ("D_BanName".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_BanName", "合计");
			} else if ("D_Unit".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Unit", listModel.size());// 总套数
			} else if ("D_position".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_position",
						listModel.stream().filter(FangYuanModel -> "1".equals(FangYuanModel.getD_Monitor())).count());
			} else if ("D_RoomNo".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_RoomNo",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2").count());
			} else if ("D_Price".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Price",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() != "2").count());
			} else if ("D_Total".equals(rec.getStr("column_name"))) {
				totalRecord.getColumns().put("D_Total",
						listModel.stream().filter(FangYuanModel -> FangYuanModel.getD_state() == "2")
								.mapToDouble(FangYuanModel::getD_Total).sum());// 总额
			} else if ("D_state".equals(rec.getStr("D_state"))) {
				totalRecord.getColumns().put("D_state", -1);
			} else {
				totalRecord.getColumns().put(rec.getStr("column_name"), "");
			}
		}

		// 整合进入list集合
		list.add(nullRecord); // 空白
		list.add(titeRecord); // 标头
		list.add(groupRecord);
		list.add(totalRecord);

		String jsonRecord = JsonKit.listToJson(list, 1);
		StringBuilder sb = new StringBuilder("{");
		sb.append("\"Rows\":" + jsonRecord + ",");
		sb.append("\"Total\":" + count + 2);
		sb.append("}");

		return sb.toString();
	}

	public ResponseEntity<byte[]> GetExcel() throws Exception{
		String sql = String.format("select r.D_BanName,r.D_Unit,r.D_RoomNo,r.D_Total,r.D_FloorArea,r.D_SerialNumber,t.D_Name,t.D_Card,t.D_Tel,r.D_Saller,r.D_state,r.D_Monitor,r.D_OperationRemark from room as r LEFT JOIN t_user as t on r.D_Operation=t.D_Tel");
		
		List<Record> records = dao.queryForRecordList(sql);	
		if(records==null || records.size()==0) {
		    	return null;
		}
		/**/
		List<FangYuanModel> listModel = fydao.queryForFangYuanList("select * from room ");
		List<Record> listRecord = new ArrayList<Record>();
		
		//标题
		Record titelRecord = new Record();
		titelRecord.getColumns().put("1", "楼号");
		titelRecord.getColumns().put("2", "总套数");
		titelRecord.getColumns().put("3", "内控");
		titelRecord.getColumns().put("4", "已售");
		titelRecord.getColumns().put("5", "待售");
		titelRecord.getColumns().put("6", "总价统计");
		listRecord.add(titelRecord);
		//数据
		Map<String, List<FangYuanModel>> groupMap =listModel.stream().collect(Collectors.groupingBy(FangYuanModel::getD_BanName));		
		List<Record> groupRecords = new ArrayList<Record>();//存放分组查询的数据		
		for (Entry<String, List<FangYuanModel>> entry : groupMap.entrySet()) {//遍历分组情况
            String key = entry.getKey();
            List<FangYuanModel> groupRecordList = entry.getValue();
            		Record groupRecord = new Record(); 
    				groupRecord.getColumns().put("1", key);//楼号
    				groupRecord.getColumns().put("2", groupRecordList.size());//总套数
    				groupRecord.getColumns().put("3", groupRecordList.stream().filter(FangYuanModel->"1".equals(FangYuanModel.getD_Monitor())).count());//内控
    				groupRecord.getColumns().put("4", groupRecordList.stream().filter(FangYuanModel->FangYuanModel.getD_state()=="2").count());//已销售
    				groupRecord.getColumns().put("5", groupRecordList.stream().filter(FangYuanModel->FangYuanModel.getD_state()!="2").count());//待售
    				groupRecord.getColumns().put("6", groupRecordList.stream().filter(FangYuanModel->FangYuanModel.getD_state()=="2").mapToDouble(FangYuanModel::getD_Total).sum());//总额    			
    		
            groupRecords.add(groupRecord);
        }
		listRecord.addAll(groupRecords);
		//统计
				Record totalRecord = new Record();
	
				totalRecord.getColumns().put("1", "合计");
				totalRecord.getColumns().put("2", listModel.size());//总套数
				totalRecord.getColumns().put("3", listModel.stream().filter(FangYuanModel->"1".equals(FangYuanModel.getD_Monitor())).count());
				totalRecord.getColumns().put("4", listModel.stream().filter(FangYuanModel->FangYuanModel.getD_state()=="2").count());
				totalRecord.getColumns().put("5", listModel.stream().filter(FangYuanModel->FangYuanModel.getD_state()!="2").count());
				totalRecord.getColumns().put("6", listModel.stream().filter(FangYuanModel->FangYuanModel.getD_state()=="2").mapToDouble(FangYuanModel::getD_Total).sum());//总额
			
				listRecord.add(totalRecord);
		/**/
		String filePath = PathKit.getWebRootPath()+"/excelTemple/开盘数据.xls";
		System.err.println(filePath);
		File file = new File(filePath);
		HSSFWorkbook workbook = writeToExcel(records,listRecord,file);
				
		return DownLoadKit.exportExcel(workbook, "开盘数据");
	}

	public HSSFWorkbook writeToExcel(List<Record> records,List<Record> tongji, File file) throws Exception {
		FileInputStream fs = new FileInputStream(file); // 读取文件
		POIFSFileSystem ps = new POIFSFileSystem(fs);// 使用POI提供的方法得到excel的信息
		HSSFWorkbook wb = new HSSFWorkbook(ps);
		HSSFSheet sheet = wb.getSheetAt(0); // 获取到工作表，因为一个excel可能有多个工作表
		// 创建数据行
		/*** 因为只有第一行有数据，下面的行没有数据，所以下面的行都需要 createRow ****/
		Record rec = null;
		for (int k = 0; k < records.size(); k++) {
			rec = records.get(k);
			HSSFRow row = sheet.createRow(k + 1); // 从第二行开始
			HSSFCell cell = row.createCell(0);// 楼栋
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_BanName"));

			cell = row.createCell(1);// 单元
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_Unit"));

			cell = row.createCell(2);// 房间号
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_RoomNo"));

			cell = row.createCell(3);// 总价
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getBigDecimal("D_Total").toString());

			cell = row.createCell(4);// 建筑面积
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getFloat("D_FloorArea"));

			cell = row.createCell(5);// 订单号
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_SerialNumber"));

			cell = row.createCell(6);// 姓名
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_Name"));

			cell = row.createCell(7);// 身份证
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_Card"));

			cell = row.createCell(8);// 手机
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_Tel"));

			cell = row.createCell(9);// 置业顾问
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_Saller"));

			cell = row.createCell(10);// 状态
			cell.setCellType(CellType.STRING);// 字符型
			if (rec.getInt("D_state") == 2 || rec.getInt("D_Monitor") == 3) {
				cell.setCellValue("已售");
			} else {
				cell.setCellValue("未售");
			}

			cell = row.createCell(11);// 购买信息
			cell.setCellType(CellType.STRING);// 字符型
			cell.setCellValue(rec.getStr("D_OperationRemark"));

		}
		
		for(int i=0;i<tongji.size();i++) {
			HSSFRow row = sheet.createRow(records.size() + 3+i); // 
			Record tjRec = tongji.get(i);
			System.err.println("i="+i);
			if(i==0) {
				for(int j=1;j<=tjRec.getColumns().size();j++) {
					HSSFCell cell = row.createCell(j);// 楼栋
					cell.setCellType(CellType.STRING);// 字符型
					cell.setCellValue(tjRec.getStr(String.valueOf(j)));	
				}				
			}else {
				for(int j=1;j<=tjRec.getColumns().size();j++) {
					HSSFCell cell = row.createCell(j);// 楼栋
					cell.setCellType(CellType.STRING);// 字符型
					if(j==1) {
						cell.setCellValue(tjRec.getStr(String.valueOf(j)));							
					}else if(j==2){
						System.err.println(tjRec.getInt("2"));
						System.err.println(tjRec.getInt(String.valueOf(j)));
						cell.setCellValue(String.valueOf(tjRec.getInt(String.valueOf(j))));	
					}else if(j==3){
						System.err.println(tjRec.getLong("3"));
						System.err.println(tjRec.getLong(String.valueOf(j)));
						cell.setCellValue(String.valueOf(tjRec.getLong(String.valueOf(j))));	
					}else if(j==4){
						System.err.println(tjRec.getLong("4"));
						System.err.println(tjRec.getLong(String.valueOf(j)));
						cell.setCellValue(String.valueOf(tjRec.getLong(String.valueOf(j))));	
					}else if(j==5){
						System.err.println(tjRec.getLong("5"));
						System.err.println(tjRec.getLong(String.valueOf(j)));
						cell.setCellValue(String.valueOf(tjRec.getLong(String.valueOf(j))));	
					}else if(j==6){
						System.err.println(tjRec.getDouble("6"));
						System.err.println(tjRec.getDouble(String.valueOf(j)));
						cell.setCellValue(String.valueOf(tjRec.getDouble(String.valueOf(j))));	
					}
				}	
			}
		}
		return wb;
	}
}
